import pandas as pd


class PandasExcelUtils:
    @staticmethod
    def read_excel(file_path, sheet_name=None, header=None):
        if header is None:
            header = [0]
        # 读取Excel文件
        df = pd.read_excel(file_path, sheet_name=sheet_name, header=header)
        return df

    @staticmethod
    def get_excel_all_sheet_names(file_path):
        """
        获取Excel文件的所有工作表名称
        :param file_path:
        :return:
        """
        xls = pd.ExcelFile(file_path)
        sheet_names = xls.sheet_names
        return sheet_names

    @staticmethod
    def get_excel_sheet_titles(file_path, sheet_name, header=None):
        """
        获取Excel文件的工作表标题
        1. 如果header为None，则默认获取第一行的标题
        2. 如果存在多级标题, 则每个标题使用元组存储
        :param file_path:
        :param sheet_name:
        :param header:
        :return:
        """
        if header is None:
            header = [0]
        df = PandasExcelUtils.read_excel(file_path, sheet_name=sheet_name, header=header)
        titles = []
        for header in header:
            level_title = df.columns.get_level_values(header)
            level_title = [title.replace('\n', ' ') if type(title) is str else title for title in level_title]
            level_title = [None if type(item) is str and item.startswith("Unnamed:") else item for item in level_title]
            titles.append(level_title)
        title_contents = list(zip(*titles))
        return title_contents


class ExcelInstance:
    def __init__(self, file_path, sheet_name=None, header=None):
        self.file_path = file_path
        self.sheet_name = sheet_name
        if sheet_name is None or type(sheet_name) is not str:
            sheet_name_list = self.get_sheet_names()
            if sheet_name_list is None or len(sheet_name_list) == 0:
                raise Exception("Excel文件没有工作表")
            self.sheet_name = sheet_name_list[0]
        self.header = header
        if header is None:
            self.header = 0
        self.df = pd.read_excel(file_path, sheet_name=self.sheet_name, header=self.header, na_values="NA")
        print(type(self.df))

    def get_sheet_names(self):
        xls = pd.ExcelFile(self.file_path)
        sheet_names = xls.sheet_names
        return sheet_names

    def get_column_labels(self):
        columns = self.df.columns
        return columns.tolist()
